package org.opsli.modulars.business.patrolRecordEXCEL.web;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.opsli.api.wrapper.business.PatrolRecordExcel.PatrolRecordModel;
import org.opsli.common.annotation.ApiRestController;
import org.opsli.core.utils.UserUtil;
import org.opsli.modulars.business.app.web.ZkUtils;
import org.opsli.modulars.business.patrolRecordEXCEL.entity.PatrolRecord;
import org.opsli.modulars.business.patrolRecordEXCEL.service.PatrolRecordService;
import org.opsli.modulars.business.patrolRecordEXCEL.service.impl.PatrolRecordServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


@Slf4j
@ApiRestController("/patrolRecord")
public class PatrolRecordController {

    @Autowired
    PatrolRecordServiceImpl prsl;

    @RequestMapping(value = "/patrol", method = RequestMethod.GET)
    public void getPatrol(HttpServletRequest request, HttpServletResponse response) {
//        String devid = "41001";
        String StartTime = "";
        String endTime = "";
        String devid = UserUtil.getSysUser().getCompanyId();//当前公司ID
        //查询所有数据
        List l = prsl.getPatrolRecord(devid, StartTime, endTime, null, null);
        //将调压箱编号存入集合，以此判断有几个调压箱
        TreeSet tr = new TreeSet();
        for (int n = 0; n < l.size(); n++) {
            PatrolRecord p = (PatrolRecord) l.get(n);
            tr.add(p.getStationName());
        }
        //将不同调压箱的巡检记录，分别存入集合
        List l2 = new ArrayList();
        for (Iterator iter = tr.iterator(); iter.hasNext(); ) {
            String a1Name = (String) iter.next();
            List l1 = new ArrayList();
            for (int b = 0; b < l.size(); b++) {
                PatrolRecord b1 = (PatrolRecord) l.get(b);
                String b1Name = b1.getStationName();
                if (b1Name.equals(a1Name)) {
                    l1.add(l.get(b));
                }
            }
            l2.add(l1);
        }

        PatrolRecordController s = new PatrolRecordController();
        //输出文件流
        OutputStream output = null;
        try {
            output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(("调压箱巡检记录" + ZkUtils.getDateStr().substring(0, 10) + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
        } catch (IOException e) {
            e.printStackTrace();
        }
        HSSFWorkbook book = null;
        try {
            book = new HSSFWorkbook(s.getExcelFile(l2));
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            book.write(output);
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            output.flush();
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
        try {
            output.close();
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
    }

    /**
     * 根据数据集生成Excel，并返回Excel文件流
     *
     * @throws IOException
     */
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;

    public InputStream getExcelFile(List l) throws IOException {
        //获取当前系统时间
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
        String sheetName = df.format(new Date());
        //创建sheet
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
        //字体样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);// 字体大小
        //字体加粗样式
        HSSFFont font0 = workbook.createFont();
        font0.setFontName("宋体");
        font0.setFontHeightInPoints((short) 16);
        font0.setBold(true);
        //通用字体样式
        HSSFCellStyle cs0 = workbook.createCellStyle();//创建第一行样式
        cs0.setAlignment(HorizontalAlignment.CENTER);
        cs0.setFont(font);
        cs0.setVerticalAlignment(VerticalAlignment.CENTER);
        //调压箱巡检记录 样式 字体加粗
        HSSFCellStyle css0 = workbook.createCellStyle();//创建第一行样式
        css0.setAlignment(HorizontalAlignment.CENTER);
        css0.setFont(font0);
        //其他数据的样式
        HSSFCellStyle cellStyleOther = workbook.createCellStyle();
        cellStyleOther.setAlignment(HorizontalAlignment.CENTER);//左右居中2
        cellStyleOther.setFont(font);
        cellStyleOther.setWrapText(true);
        cellStyleOther.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleOther.setBorderBottom(BorderStyle.THIN);
        cellStyleOther.setBorderLeft(BorderStyle.THIN);
        cellStyleOther.setBorderRight(BorderStyle.THIN);
        cellStyleOther.setBorderTop(BorderStyle.THIN);

        /*外观-是否漏气-切断是否正常-调压是否正常-卫生情况-压力表是否正常
         * 通用样式
         * */
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);//左右居中
        cellStyle2.setFont(font);
        cellStyle2.setBorderBottom(BorderStyle.THIN);
        cellStyle2.setBorderLeft(BorderStyle.THIN);
        cellStyle2.setBorderRight(BorderStyle.THIN);
        cellStyle2.setBorderTop(BorderStyle.THIN);
        //创建单元格时用到的默认的行数
        int exRow0 = 0;
        int exRow1 = 1;
        int exRow2 = 2;
        int exRow3 = 3;
        int exRow4 = 4;
        int exRow5 = 5;
        int exRow11 = 11;
        int exRow12 = 12;
        int exRow13 = 13;
        int exRow06 = 6;
        int exRow07 = 7;
        int exRow08 = 8;
        int exRow09 = 9;
        int exRow010 = 10;
        //数据列表默认7条数据(脚标2~8)
        int dataNumber = 8;
        //循环创建表格，生成数据
        for (int c = 0; c < l.size(); c++) {
            List l3 = (List) l.get(c);
            int nowNumber = l3.size() + 1;
            if (nowNumber > dataNumber) {
                dataNumber = nowNumber;
            }
            //调压箱巡检记录
            //exRow0
            HSSFRow h0 = sheet.createRow(exRow0);//创建一行
            HSSFCell c0 = h0.createCell(0);//在第一行创建一个cell
            c0.setCellValue("调压箱巡检记录");
            c0.setCellStyle(css0);
            CellRangeAddress cra0 = new CellRangeAddress(exRow0, exRow0, 0, dataNumber);//合并行
            sheet.addMergedRegion(cra0);
//            setBorderStyle(1, cra0, sheet, workbook);

            //小区名称
            //exRow1
            HSSFRow h1 = sheet.createRow(exRow1);//获取第二行
            HSSFCell c1 = h1.createCell(0);//在第二行创建一个cell
            c1.setCellValue("小区名称");
            c1.setCellStyle(cellStyle2);
            CellRangeAddress cra1 = new CellRangeAddress(exRow1, exRow1, 0, 1);
            sheet.addMergedRegion(cra1);
            setBorderStyle(1, cra1, sheet, workbook);
            //小区名称后的值
            CellRangeAddress cra11 = new CellRangeAddress(exRow1, exRow1, 2, dataNumber);
            sheet.addMergedRegion(cra11);
            setBorderStyle(1, cra11, sheet, workbook);
            //调压箱编号
            //exRow2
            HSSFRow h2 = sheet.createRow(exRow2);//获取第三行
            HSSFCell c2 = h2.createCell(0);//在第三行创建一个cell
            c2.setCellValue("调压箱编号");
            c2.setCellStyle(cellStyle2);
            CellRangeAddress cra2 = new CellRangeAddress(exRow2, exRow2, 0, 1);
            sheet.addMergedRegion(cra2);
            setBorderStyle(1, cra2, sheet, workbook);
            //调压箱编号后的值
            CellRangeAddress cra22 = new CellRangeAddress(exRow2, exRow2, 2, dataNumber);
            sheet.addMergedRegion(cra22);
            setBorderStyle(1, cra22, sheet, workbook);
            //管理楼栋
            //exRow3
            HSSFRow h3 = sheet.createRow(exRow3);//获取第四行
            HSSFCell c3 = h3.createCell(0);//在第四行创建一个cell
            c3.setCellValue("管理楼栋");
            c3.setCellStyle(cs0);
            CellRangeAddress cra3 = new CellRangeAddress(exRow3, exRow3, 0, 1);
            sheet.addMergedRegion(cra3);
            setBorderStyle(1, cra3, sheet, workbook);
            //管理楼栋后的值
            CellRangeAddress cra33 = new CellRangeAddress(exRow3, exRow3, 2, dataNumber);
            sheet.addMergedRegion(cra33);
            setBorderStyle(1, cra33, sheet, workbook);
            //巡检日期
            //exRow4
            HSSFRow h4 = sheet.createRow(exRow4);//获取第五行
            HSSFCell c4 = h4.createCell(0);//在第五行创建一个cell
            c4.setCellValue("巡检日期");
            c4.setCellStyle(cs0);
            CellRangeAddress cra4 = new CellRangeAddress(exRow4, exRow4, 0, 1);
            sheet.addMergedRegion(cra4);
            setBorderStyle(1, cra4, sheet, workbook);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = h4.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //巡检内容
            //exRow5
            HSSFRow row = sheet.createRow(exRow5);
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            HSSFCell cell = row.createCell(0);
            cellStyle1.setFont(font);                         //设置字体
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);          // 左右居中2 居右3 默认居左
            cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
            cellStyle1.setWrapText(true);//先设置为自动换行
            cellStyle1.setBorderBottom(BorderStyle.THIN);//下边框
            cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
            cell.setCellStyle(cellStyle1);
            cell.setCellValue(new HSSFRichTextString("巡检内容"));
            int exRow55 = exRow5 + 5;
            sheet.addMergedRegion(new CellRangeAddress(exRow5, exRow55, 0, 0));
            //设置第二列列宽
            sheet.setColumnWidth(1, 7000);
            //外观
            HSSFCell cell2 = row.createCell(1);//设置开始的列
            cell2.setCellValue("外观");
            cell2.setCellStyle(cellStyle2);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = row.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //是否漏气
            //exRow06
            HSSFRow row6 = sheet.createRow(exRow06);
            HSSFCell cell6 = row6.createCell(1);//设置开始的列
            cell6.setCellValue("是否漏气");
            cell6.setCellStyle(cellStyle2);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = row6.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //切断是否正常
            //exRow07
            HSSFRow row7 = sheet.createRow(exRow07);
            HSSFCell cell7 = row7.createCell(1);//设置开始的列
            cell7.setCellValue("切断是否正常");
            cell7.setCellStyle(cellStyle2);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = row7.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //调压是否正常
            //exRow08
            HSSFRow row8 = sheet.createRow(exRow08);
            HSSFCell cell8 = row8.createCell(1);//设置开始的列
            cell8.setCellValue("调压是否正常");
            cell8.setCellStyle(cellStyle2);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = row8.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //卫生情况
            //exRow09
            HSSFRow row9 = sheet.createRow(exRow09);
            HSSFCell cell9 = row9.createCell(1);//设置开始的列
            cell9.setCellValue("卫生情况");
            cell9.setCellStyle(cellStyle2);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = row9.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //压力表是否正常
            //exRow010
            HSSFRow row10 = sheet.createRow(exRow010);
            HSSFCell cell10 = row10.createCell(1);//设置开始的列
            cell10.setCellValue("压力表是否正常");
            cell10.setCellStyle(cellStyle2);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = row10.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //地埋管道情况
            //exRow11
            HSSFRow h11 = sheet.createRow(exRow11);//获取第二行
            HSSFCell c11 = h11.createCell(0);//在第二行创建一个cell
            c11.setCellValue("地埋管道情况");
            c11.setCellStyle(cs0);
            CellRangeAddress cra111 = new CellRangeAddress(exRow11, exRow11, 0, 1);
            sheet.addMergedRegion(cra111);
            setBorderStyle(1, cra111, sheet, workbook);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = h11.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //其他
            //exRow12
            HSSFRow h12 = sheet.createRow(exRow12);//获取第二行
            HSSFCell c12 = h12.createCell(0);//在第二行创建一个cell
            c12.setCellValue("其他");
            c12.setCellStyle(cs0);
            CellRangeAddress cra121 = new CellRangeAddress(exRow12, exRow12, 0, 1);
            sheet.addMergedRegion(cra121);
            setBorderStyle(1, cra121, sheet, workbook);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = h12.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //检查人
            //exRow13
            HSSFRow h13 = sheet.createRow(exRow13);//获取第二行
            HSSFCell c13 = h13.createCell(0);//在第二行创建一个cell
            c13.setCellValue("检查人");
            c13.setCellStyle(cs0);
            CellRangeAddress cra131 = new CellRangeAddress(exRow13, exRow13, 0, 1);
            sheet.addMergedRegion(cra131);
            setBorderStyle(1, cra131, sheet, workbook);
            for (int j = 2; j <= dataNumber; j++) {
                HSSFCell cellf = h13.createCell(j);
                cellf.setCellStyle(cellStyle2);
            }
            //写入数据
            for (int i = 0; i < l3.size(); i++) {
                if (i == 0) {
                    PatrolRecord pp = (PatrolRecord) l3.get(i);
                    //小区名称
                    HSSFCell XQName = h1.createCell(2);
                    XQName.setCellValue(pp.getVillageName());
                    XQName.setCellStyle(cellStyle2);
                    //调压箱编号
                    HSSFCell TYNumber = h2.createCell(2);
                    TYNumber.setCellValue(pp.getStationName());
                    TYNumber.setCellStyle(cellStyle2);
                    //管理楼栋
                    HSSFCell GLFloor = h3.createCell(2);
                    GLFloor.setCellValue(pp.getFloorName());
                    GLFloor.setCellStyle(cellStyle2);
                    break;
                }
            }
            //写入数据
            for (int i = 0; i < l3.size(); i++) {
                PatrolRecord p = (PatrolRecord) l3.get(i);
                int n = 2 + i;//单元格索引
                //巡检日期
                HSSFCell XJDate = h4.createCell(n);
                String date = p.getLastTime().substring(0, 10);
                XJDate.setCellValue(date);
                XJDate.setCellStyle(cellStyle2);
                //外观
                HSSFCell WGuan = row.createCell(n);
                int wg = p.getCondition1();
                if (wg == 0) {
                    WGuan.setCellValue("正常");
                } else if (wg == 1) {
                    WGuan.setCellValue("锈蚀");
                } else {
                    WGuan.setCellValue("无门");
                }
                WGuan.setCellStyle(cellStyle2);
                //是否漏气
                HSSFCell SFLQ = row6.createCell(n);
                int lq = p.getLeakageCondition();
                if (lq == 0) {
                    SFLQ.setCellValue("未泄露");
                } else {
                    SFLQ.setCellValue("轻微泄露");
                }
                SFLQ.setCellStyle(cellStyle2);
                //切断是否正常
                HSSFCell QDSFZC = row7.createCell(n);
                int qd = p.getCondition2();
                if (qd == 0) {
                    QDSFZC.setCellValue("正常");
                } else {
                    QDSFZC.setCellValue("不正常");
                }
                QDSFZC.setCellStyle(cellStyle2);
                //调压是否正常
                HSSFCell TYSFZC = row8.createCell(n);
                int ty = p.getCondition3();
                if (ty == 0) {
                    TYSFZC.setCellValue("正常");
                } else {
                    TYSFZC.setCellValue("不正常");
                }
                TYSFZC.setCellStyle(cellStyle2);
                //卫生情况
                HSSFCell WSQK = row9.createCell(n);
                int ws = p.getHygieneCondition();
                if (ws == 0) {
                    WSQK.setCellValue("良好");
                } else {
                    WSQK.setCellValue("有杂物");
                }
                WSQK.setCellStyle(cellStyle2);
                //压力表是否正常
                HSSFCell YLBSFZC = row10.createCell(n);
                int yl = p.getCondition4();
                if (yl == 0) {
                    YLBSFZC.setCellValue("正常");
                } else {
                    YLBSFZC.setCellValue("不正常");
                }
                YLBSFZC.setCellStyle(cellStyle2);
                //地埋管道情况
                HSSFCell DMGDQK = h11.createCell(n);
                int dm = p.getCondition5();
                if (dm == 0) {
                    DMGDQK.setCellValue("不漏气");
                } else {
                    DMGDQK.setCellValue("漏气");
                }
                DMGDQK.setCellStyle(cellStyle2);
                //其他
                HSSFCell QTa = h12.createCell(n);
                QTa.setCellValue(p.getCondition6());
                QTa.setCellStyle(cellStyleOther);
                //检查人
                HSSFCell JCRen = h13.createCell(n);
                JCRen.setCellValue(p.getUserName());
                JCRen.setCellStyle(cellStyle2);
            }
            //设置数据显示列宽
            for (int s = 2; s <= dataNumber; s++) {
                sheet.setColumnWidth(s, 4300);
            }
            //多个调压箱，需要创建多个表格，相应的行数就要一次增加
            exRow0 += 20;
            exRow1 += 20;
            exRow2 += 20;
            exRow3 += 20;
            exRow4 += 20;
            exRow5 += 20;
            exRow11 += 20;
            exRow12 += 20;
            exRow13 += 20;
            exRow06 += 20;
            exRow07 += 20;
            exRow08 += 20;
            exRow09 += 20;
            exRow010 += 20;
            //数据不超过7条的调压箱记录，依旧使用默认七条数据格式
            dataNumber = 8;
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);
        return bais;
    }

    //加边框
    private static void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb) {
        HSSFCellStyle cs = wb.createCellStyle(); // 样式对象
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        cs.setFont(font);
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setBorderBottom(BorderStyle.valueOf((short) border));
        cs.setBorderTop(BorderStyle.valueOf((short) border));
        cs.setBorderLeft(BorderStyle.valueOf((short) border));
        cs.setBorderRight(BorderStyle.valueOf((short) border));
        setRegionStyle(cs, region, sheet);
    }

    private static void setRegionStyle(HSSFCellStyle cs, CellRangeAddress region, HSSFSheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }
}
